#! /bin/bash
mysql -uroot -p123456 -e"online_edu;"
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
-- 每天/每月/每年线上线下以及新老学员的意向用户个数
-- 每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
-- 每天/每月/每年各学科线上线下以及新老学员的意向用户个数Top10
-- 每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10
-- 每天/每月/每年各来源渠道线上线下以及新老学员的意向用户个数
-- 每天/每月/每年==各咨询中心==线上线下以及新老学员的意向用户个数
-- > 以传智咨询为例 分为总部咨询 各个分校咨询
--   相当于计算传智每个咨询部门的一些指标
--  > 也可以理解为就是销售人员所属的部门
-- 每天线上线下及新老学员的有效线索个数
--  > 是否可以联系成功  申述表
-- 每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数

-- case when grouping(department_name) = 0  then 'department'
-- when grouping(origin_channel)=0 then 'origin_channel'
-- when grouping(itcast_school_id)=0 then 'school'
-- when grouping(itcast_subject_id)=0 then 'subject'
-- when grouping(area)=0 then 'area'
-- when grouping(O2O,customer_mark)=0 then 'O2O_customer_mark'
-- when grouping(O2O)=0 then 'O2O'
-- else 'all' end as group_type,

-- case when grouping(hourinfo)=0 then 'hour'
-- when grouping(dayinfo)=0 then 'day'
-- when grouping(monthinfo)=0 then 'month'
-- when grouping(yearinfo)=0 then 'year'
-- else null end as time_type


 --- 每天/每月/每年线上线下以及新老学员的意向用户个数
 select
yearinfo,
O2O,
customer_mark,
intention_customer_count
 from intention_dws.dws_intention2 where group_type='O2O_customer_mark' and time_type='year';

  select
yearinfo,
monthinfo,
O2O,
customer_mark,
intention_customer_count
 from intention_dws.dws_intention2 where group_type='O2O_customer_mark' and time_type='month';

 --- 每天/每月/每年各地区的线上线下以及新老学员的意向用户个数 ok
 create table mysql.online_edu.intention_area_year_customer_count_0508 as
 select
 yearinfo,
 area,
 SUBSTRING(area, 1,2) as country,
 SUBSTRING (area,4,3) as province,
 SUBSTRING (area, 7,5) as city,
 intention_customer_count
 from intention_dws.dws_intention2 where group_type='area' and time_type='year';

-- 每年的意向用户数
 select
 yearinfo,
 intention_customer_count
 from intention_dws.dws_intention2 where group_type='all' and time_type='year';


 -- 每天/每月/每年各学科线上线下以及新老学员的意向用户个数Top10
 with t as (
SELECT
yearinfo,
itcast_subject_id,
itcast_subject_name,
intention_customer_count
 from intention_dws.dws_intention2 where group_type='subject' and time_type='year' and itcast_subject_id is not null)


select
yearinfo,
itcast_subject_id,
itcast_subject_name,
dense_rank() over(partition by yearinfo order by intention_customer_count desc) rn
from t;

--- 每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10  ok
create table mysql.online_edu.intention_school_year_customer_count_0508 as
with t as (
SELECT
yearinfo,
itcast_school_id,
itcast_school_name,
intention_customer_count
from intention_dws.dws_intention2 where group_type='school' and time_type='year' and itcast_school_id is not null)


select
yearinfo,
itcast_school_id,
itcast_school_name,
intention_customer_count,
dense_rank() over(partition by yearinfo order by intention_customer_count desc) rn
from t;

--- 每天/每月/每年==各咨询中心==线上线下以及新老学员的意向用户个数 ok
create table mysql.online_edu.intention_department_year_customer_count_0508 as
select
yearinfo,
department_name,
intention_customer_count
from intention_dws.dws_intention2 where group_type='department' and time_type='year';

--- 每天线上线下及新老学员的有效线索个数
select
yearinfo,
monthinfo,
dayinfo,
O2O,
customer_mark,
valid_clue_count
from intention_dws.dws_clue_valid2 where group_type='O2O_customer_mark' and time_type='day';

--- 每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
with a as (
select
yearinfo,
monthinfo,
dayinfo,
hourinfo,
O2O,
customer_mark,
valid_clue_count
from intention_dws.dws_clue_valid2 where group_type='O2O_customer_mark' and time_type='hour'),

b as (
select
yearinfo,
monthinfo,
dayinfo,
hourinfo,
O2O,
customer_mark,
clue_count
from intention_dws.dws_clue2 where group_type='O2O_customer_mark' and time_type='hour')

select
*
from a join b on a.yearinfo = b.yearinfo and a.monthinfo = b.monthinfo and a.dayinfo = b.dayinfo and a.hourinfo = b.hourinfo
and a.O2O = b.O2O and a.customer_mark = b.customer_mark;

